﻿using ClosedXML.Excel;

namespace EmployeeLeavePackage
{
    public static class ExcelHelper
    {
        /// <summary>
        /// 读取Excel数据
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public static List<EmployeeLeave> Read(string filePath)
        {
            var readDataList = new List<EmployeeLeave>();
            using (var workbook = new XLWorkbook(filePath))
            {
                var worksheet = workbook.Worksheet(1);
                var headerRow = worksheet.RowsUsed().FirstOrDefault(r => r.RowNumber() == 1);
                var headers = headerRow?.Cells().Select((cell, index) => new
                {
                    Index = index + 1,
                    Name = cell.Value.ToString().Replace("\r", "").Replace("\n", "")
                }).ToList();
                if (headers?.Count > 0)
                {
                    var dataRows = worksheet.RowsUsed().Where(r => r.RowNumber() > 1 && !r.IsEmpty());
                    foreach (var row in dataRows)
                    {
                        var rowDict = new Dictionary<string, string>();
                        foreach (var header in headers)
                        {
                            string headerName, key;
                            headerName = key = header.Name;
                            var value = row.Cell(header.Index).Value.ToString();
                            rowDict[key] = value;
                        }
                        var temp = new EmployeeLeave
                        {
                            EmployeeNumber = rowDict["Employee No."],
                            Code = rowDict["Leave Policy Code"],
                            Date = DateTime.TryParse(rowDict["Effective Date"], out var date) ? date.ToString("yyyy-MM-dd") : "",
                            EndDate = rowDict.TryGetValue("End Date", out string? endDateStr) ? (DateTime.TryParse(endDateStr, out var endDate) ? endDate.ToString("yyyy-MM-dd") : "9999-12-31") : "9999-12-31",
                        };
                        readDataList.Add(temp);
                    }
                }
            }
            return readDataList;
        }
    }
}